package com.ray.controller.base;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.github.liaochong.myexcel.core.DefaultExcelBuilder;
import com.github.liaochong.myexcel.utils.FileExportUtil;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.ray.interceptor.base.AopContext;
import com.ray.interceptor.base.BizInterceptConfig;
import com.ray.interceptor.base.MetaObjectIntercept;
import com.ray.log.WebLog;
import com.ray.model.DataField;
import com.ray.model.DataObject;
import com.ray.model.Dicts;
import com.ray.services.TemplateService;
import com.ray.util.Commen;
import com.ray.util.Ret;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 公共控制器
 *
 * @author Ray
 */
@RestController
@RequestMapping("/common")
public class CommonController extends BaseController {

    @Autowired
    protected MetaObjectIntercept intercept;

    @Autowired
    private BizInterceptConfig bizInterceptConfig;

    @Autowired
    private TemplateService templateService;

    /**
     * @note 更新字典
     * @time 2024年03月25日 10:17:38
     * @author Ray
     */
    @RequestMapping("updateDicts")
    @WebLog(description = "更新字典")
    @Transactional("baseTransactionManager")
    public Ret updateDicts() {
        Db.use(base).update("TRUNCATE dicts");
        String[] data_soucre = new String[]{main, base};
        for (int j = 0; j < data_soucre.length; j++) {
            List<Record> tableList = Db.use(sys).find("select table_name from tables where table_schema = ?", data_soucre[j]);
            for (int l = 0; l < tableList.size(); l++) {
                List<Record> list = Db.use(sys)
                        .find("select * from columns where table_schema = ?"
                                + " and table_name = ? order by ORDINAL_POSITION", data_soucre[j], tableList.get(l).get("TABLE_NAME"));
                for (int i = 0; i < list.size(); i++) {
                    if ("int".equals(list.get(i).get("DATA_TYPE")) && !"PRI".equals(list.get(i).get("COLUMN_KEY"))) {
                        String comment = list.get(i).getStr("COLUMN_COMMENT");
                        String[] temp = comment.split(":");
                        if (temp.length == 2) {
                            String[] temp1 = temp[1].split(",");
                            for (int k = 0; k < temp1.length; k++) {
                                String[] dict = temp1[k].split("=");
                                Dicts dicts = new Dicts();
                                dicts.setField(list.get(i).getStr("COLUMN_NAME"));
                                dicts.setObject(tableList.get(l).get("TABLE_NAME"));
                                dicts.setName(dict[1]);
                                dicts.setValue(Integer.valueOf(dict[0]));
                                dicts.save();
                            }
                        }
                    }
                }
            }
        }
        return Ret.ok("msg", "更新字典成功");
    }

    /**
     * @note 导出表数据
     * @time 2024年03月26日 14:28:54
     * @author Ray
     */
    @RequestMapping("export")
    @WebLog(description = "导出表数据")
    public Ret export(@RequestBody JSONObject param) throws Exception {
        String region = getSessionUserRegion();
        DataObject model = dataObjectService.getDataObject(param.getString("data_object_no"));
        List<DataField> fields = dataObjectService.getDataFields(param.getString("data_object_no"));
        String sqlString = "select ";
        for (int i = 0; i < fields.size(); i++) {
            if (i > 0) {
                sqlString += ",";
            }
            if (fields.get(i).getIsFictitious()) {
                if (fields.get(i).getFictitiousSql() != null) {
                    sqlString += "(" + fields.get(i).getFictitiousSql() + ") as " + fields.get(i).getEn();
                } else {
                    sqlString += "'/' as " + fields.get(i).getEn();
                }
            } else {
                if ("select".equals(fields.get(i).getType()) || "radio".equals(fields.get(i).getType())) {
                    sqlString += "(select " + fields.get(i).getTypeConfig().split("\\|")[1] + " " + fields.get(i).getTypeConfig().split("\\|")[0];
                    if ("where".indexOf(fields.get(i).getTypeConfig().split("\\|")[0].toLowerCase()) != -1) {
                        sqlString += " where 1=1";
                    }
                    ;
                    sqlString += " and " + fields.get(i).getTypeConfig().split("\\|")[2] + " = t." + fields.get(i).getEn() + ") as " + fields.get(i).getEn();
                } else if ("selector".equals(fields.get(i).getType())) {
                    JSONObject config = JSONObject.parseObject(fields.get(i).getTypeConfig());
                    if ("sql".equals(config.getString("type"))) {
                        sqlString = sqlString + "(SELECT GROUP_CONCAT(" + config.getString("value") + ") FROM (" + config.getString("sql") + ") temp WHERE FIND_IN_SET(" + config.getString("key") + ", t." + ((DataField) fields.get(i)).getEn() + ")) " + ((DataField) fields.get(i)).getEn();
                        sqlString = sqlString + "," + ((DataField) fields.get(i)).getEn() + " as " + ((DataField) fields.get(i)).getEn() + "_value";
                    } else {
                        sqlString = sqlString + ((DataField) fields.get(i)).getEn();
                    }
                } else if ("user".equals(fields.get(i).getType())) {
                    sqlString = sqlString + "(SELECT GROUP_CONCAT(username) FROM " + base + ".user WHERE is_delete = 0 and region = '" + region + "' and FIND_IN_SET(userid, t." + fields.get(i).getEn() + ")) " + fields.get(i).getEn();
                    sqlString = sqlString + "," + fields.get(i).getEn() + " as " + fields.get(i).getEn() + "_value";
                } else {
                    sqlString += fields.get(i).getEn();
                }
            }
        }
        sqlString += " from " + model.getTableName() + " t where t.region = '" + region + "' and is_delete=0";

        JSONObject query = param.getJSONObject("queryForm");
        if (query != null) {
            for (int i = 0; i < fields.size(); i++) {
                if (fields.get(i).getIsQuery() && !Commen.isEmptyy(query.get(fields.get(i).getEn()))) {
                    if (fields.get(i).getIsFictitious()) {
                        sqlString = sqlString + " and (" + ((DataField) fields.get(i)).getFictitiousSql() + ") like '" + query.get(((DataField) fields.get(i)).getEn()) + "'";
                    } else if ("input".equals(fields.get(i).getType())) {
                        sqlString = sqlString + " and " + ((DataField) fields.get(i)).getEn() + " like '" + query.get(((DataField) fields.get(i)).getEn()) + "'";
                    } else if ("select".equals(fields.get(i).getType()) || "radio".equals(fields.get(i).getType())) {
                        if (!"".equals(query.get(fields.get(i).getEn()))) {
                            sqlString += " and " + fields.get(i).getEn() + " = " + query.get(fields.get(i).getEn());
                        }
                    } else if ("date".equals(fields.get(i).getType()) || "datetime".equals(fields.get(i).getType())) {
                        String dateRange = query.get(fields.get(i).getEn()).toString();
                        String start = dateRange.split(",")[0].split("\"")[1];
                        String end = dateRange.split(",")[1].split("\"")[1];
                        sqlString += " and " + fields.get(i).getEn() + " >= '" + start + "'";
                        sqlString += " and " + fields.get(i).getEn() + " <= '" + end + "'";
                    }
                }
            }
        }
        intercept = bizInterceptConfig.initMetaObjectIntercept(model.getInterceptor());
        if (intercept != null) {
            AopContext ac = new AopContext(user());
            ac.sql = sqlString;
            sqlString = intercept.queryBefore(ac);
        }

        sqlString += model.getWhereAttr() != null ? model.getWhereAttr() : "";
        //region,userid模板
        sqlString = sqlString.replaceAll("#region", region)
                .replaceAll("#userid", getSessionUserId())
                .replaceAll("#datasource_main", main)
                .replaceAll("#datasource_base", base);
        //获取数据
        List<Record> list = dbService.find(model.getDataSource(), model.getTableName(), sqlString, true);
        //初始化表头
        JSONObject body = param.getJSONObject("body");
        JSONArray fieldsArray = body.getJSONArray("fields");
        Map<String, String> headerMap = new LinkedHashMap<>();
        for (int i = 0; i < fieldsArray.size(); i++) {
            JSONObject field = fieldsArray.getJSONObject(i);
            headerMap.put(field.getString("field"), field.getString("title"));
        }
        List<Map> dataMapList = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> temp = list.get(i).getColumns();
            dataMapList.add(temp);
        }
        List<String> titles = new ArrayList(headerMap.values());
        List<String> orders = new ArrayList(headerMap.keySet());
        Workbook workbook = DefaultExcelBuilder.of(Map.class)
                .sheetName("导出")
                .titles(titles)
                .widths(10, 20)
                .fieldDisplayOrder(orders)
                .fixedTitles()
                .build(dataMapList);
        String filename = body.getString("filename") + "_" + new SimpleDateFormat("yyyy-MM-dd_HH_mm_ss").format(new Date()) + ".xlsx";
        File file = new File(domin_path + "/export/" + filename);
        FileExportUtil.export(workbook, file);
        return Ret.ok("msg", "导出成功").set("url", domin_url + "/export/" + filename);
    }

	/*@RequestMapping("export")
	@WebLog(description = "导出表数据")
	public Ret export(@RequestBody JSONObject param) {
		String region = getSessionUserRegion();
		DataObject model = dataObjectService.getDataObject(param.getString("data_object_no"));
		List<DataField> fields = dataObjectService.getDataFields(param.getString("data_object_no"));
		String sqlString = "select ";
		for (int i = 0; i < fields.size(); i++) {
			if(i>0) {
				sqlString += ",";
			}
			if (fields.get(i).getIsFictitious()) {
				if(fields.get(i).getFictitiousSql()!=null) {
					sqlString += "(" + fields.get(i).getFictitiousSql() + ") as " + fields.get(i).getEn();
				}else {
					sqlString += "'/' as "+fields.get(i).getEn();
				}
			}else {
				if("select".equals(fields.get(i).getType()) || "radio".equals(fields.get(i).getType())) {
					sqlString += "(select "+fields.get(i).getTypeConfig().split("\\|")[1]+" "+fields.get(i).getTypeConfig().split("\\|")[0];
					if("where".indexOf(fields.get(i).getTypeConfig().split("\\|")[0].toLowerCase())!=-1) {
						sqlString += " where 1=1";
					};
					sqlString += " and "+fields.get(i).getTypeConfig().split("\\|")[2]+" = t."+fields.get(i).getEn()+") as "+fields.get(i).getEn();
				}else if("selector".equals(fields.get(i).getType())) {
					JSONObject config = JSONObject.parseObject(fields.get(i).getTypeConfig());
					if ("sql".equals(config.getString("type"))) {
						sqlString = sqlString + "(SELECT GROUP_CONCAT(" + config.getString("value") + ") FROM (" + config.getString("sql") +") temp WHERE FIND_IN_SET(" + config.getString("key") + ", t." + ((DataField)fields.get(i)).getEn() + ")) " + ((DataField)fields.get(i)).getEn();
						sqlString = sqlString + "," + ((DataField)fields.get(i)).getEn() + " as " + ((DataField)fields.get(i)).getEn() + "_value";
					} else {
						sqlString = sqlString + ((DataField)fields.get(i)).getEn();
					}
				}else if("user".equals(fields.get(i).getType())) {
					sqlString = sqlString + "(SELECT GROUP_CONCAT(username) FROM " + base + ".user WHERE is_delete = 0 and region = '"+region+"' and FIND_IN_SET(userid, t." + fields.get(i).getEn() + ")) " + fields.get(i).getEn();
					sqlString = sqlString + "," + fields.get(i).getEn() + " as " + fields.get(i).getEn() + "_value";
				}else {
					sqlString += fields.get(i).getEn();
				}
			}
		}
		sqlString += " from "+model.getTableName()+" t where t.region = '"+region+"' and is_delete=0";

		JSONObject query = param.getJSONObject("queryForm");
		if(query!=null) {
			for (int i = 0; i < fields.size(); i++) {
				if (*//* fields.get(i).getIsQuery() && *//*!Commen.isEmptyy(query.get(fields.get(i).getEn()))) {
					if(fields.get(i).getIsFictitious()) {
						sqlString = sqlString + " and (" + ((DataField)fields.get(i)).getFictitiousSql() + ") like '" + query.get(((DataField)fields.get(i)).getEn()) + "'";
					}else if("input".equals(fields.get(i).getType())) {
						sqlString = sqlString + " and " + ((DataField)fields.get(i)).getEn() + " like '" + query.get(((DataField)fields.get(i)).getEn()) + "'";
					}else if("select".equals(fields.get(i).getType()) || "radio".equals(fields.get(i).getType())) {
						if(!"".equals(query.get(fields.get(i).getEn()))) {
							sqlString += " and "+fields.get(i).getEn()+" = "+query.get(fields.get(i).getEn());
						}
					}else if("date".equals(fields.get(i).getType()) || "datetime".equals(fields.get(i).getType())) {
						String dateRange = query.get(fields.get(i).getEn()).toString();
						String start = dateRange.split(",")[0].split("\"")[1];
						String end = dateRange.split(",")[1].split("\"")[1];
						sqlString += " and "+fields.get(i).getEn()+" >= '"+start+"'";
						sqlString += " and "+fields.get(i).getEn()+" <= '"+end+"'";
					}
				}
			}
		}
		*//**
     * query before
     *//*
		try {
			intercept = bizInterceptConfig.initMetaObjectIntercept(model.getInterceptor());
			if (intercept != null) {
				AopContext ac = new AopContext(user());
				ac.sql = sqlString;
				sqlString = intercept.queryBefore(ac);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return Ret.fail("msg", e.getMessage());
		}

		sqlString += model.getWhereAttr()!=null?model.getWhereAttr():"";
		//region,userid模板
		sqlString = sqlString.replaceAll("#region", region)
				.replaceAll("#userid", getSessionUserId())
				.replaceAll("#datasource_main",main)
				.replaceAll("#datasource_base",base);
		//获取数据
		dbService.exportAsync(model.getDataSource(), model.getTableName(),sqlString,param.getJSONObject("body"));
		//Db.use(model.getDataSource()).find(sqlString);
		return Ret.ok("后台导出数据中，请到后台执行列表进行查看");
	}*/

    /**
     * @note 导入数据
     * @time 2024年03月26日 14:41:22
     * @author Ray
     */
    @RequestMapping("importData")
    @WebLog(description = "导入数据")
    public Ret importData(@RequestParam MultipartFile file, String data_object_no) throws Exception {
        //excel上传
        InputStream fs = null;
        try {
            fs = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        XSSFWorkbook workbook = null;
        try {
            workbook = new XSSFWorkbook(fs);
        } catch (IOException e) {
            e.printStackTrace();
        }
        List<Record> list = new ArrayList<Record>();
        XSSFSheet hs = workbook.getSheetAt(0);
        Record res = getList(hs);
        if (res.getBoolean("statu")) {
            list = res.get("list");
            if (list.size() > 0) {
                DataObject object = dataObjectService.getDataObject(data_object_no);
                AopContext ac = new AopContext(user(), list);
                MetaObjectIntercept intercept = bizInterceptConfig.initMetaObjectIntercept(object.getInterceptor());
                /**
                 * import before
                 */
                if (intercept != null) {
                    Ret ret = intercept.importBefore(ac);
                    if (!Commen.isEmptyy(ret)) {
                        return ret;
                    }
                    templateService.importAndImportafter(object, list,intercept,ac);
                }else {
                    dbService.batchSave(object.getDataSource(), object.getTableName(), list);
                }
            }
            return Ret.ok("msg", "导入数据成功");
        } else {
            return Ret.fail(res.get("msg"));
        }
    }

    /**
     * 将sheet内容转换为List<Record>
     *
     * @param hs
     * @return
     */
    public Record getList(XSSFSheet hs) {
        int rowNum = 0;
        Record record = new Record();
        DataFormatter objDefaultFormat = new DataFormatter();
        XSSFRow row = null;
        try {
            List<Record> list = new ArrayList<Record>();
            //获取Sheet最后一行
            int last = hs.getLastRowNum();
            //获取表头字段
            XSSFRow title = hs.getRow(0);
            //遍历获取单元格里的信息
            for (int i = 2; i <= last; i++) {
                rowNum = i;
                row = hs.getRow(i);
                int firstCellNum = row.getFirstCellNum();//获取所在行的第一个列号
                int lastCellNum = row.getLastCellNum();//获取所在行的最后一个列号
                Record r = new Record();
                for (int j = firstCellNum; j < lastCellNum; j++) {
                    XSSFCell cell = row.getCell(j);
                    if (cell != null && objDefaultFormat.formatCellValue(cell) != "") {
                        r.set(title.getCell(j).getStringCellValue(), objDefaultFormat.formatCellValue(cell));
                    }
                }
                r.set("region", getSessionUserRegion());
                r.set("create_user_id", getSessionUserId());
                r.set("create_user_name", getSessionUsername());
                list.add(r);
            }
            record.set("list", list);
            record.set("statu", true);
            return record;
        } catch (Exception e) {
            e.printStackTrace();
            record.set("statu", false);
            record.set("msg", "导入错误：错误sheet[" + hs.getSheetName() + "],错误行数[" + (rowNum + 1) + "]");
            return record;
        }
    }

}
